﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Common;

namespace DAO
{
    public class DonViTinhDAO
    {
        /// <summary>
        /// Lấy tât cả bản ghi của bảng Đơn Vị Tính
        /// </summary>
        /// <returns>Table</returns>
        public static DataTable SelectAll()
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_SelectAllDonViTinh", conn);                
                sqlComm.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(sqlComm);
                DataTable dt = new DataTable();
                da.Fill(dt);
                da.Dispose();
                sqlComm.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Lấy 1 bản ghi của bảng Đơn Vị Tính
        /// </summary>
        /// <param name="id">iDonViTinh_ID</param>
        /// <returns>object DonViTinh</returns>
        public static DonViTinh SelectById(int id)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_SelectByIdDonViTinh", conn);
                sqlComm.CommandType = CommandType.StoredProcedure; 
                sqlComm.Parameters.Add("@iDonViTinh_ID", SqlDbType.Int).Value = id;
                DonViTinh rtDVT = new DonViTinh(sqlComm.ExecuteReader());
                sqlComm.Dispose();
                return rtDVT;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Update mot ban ghi trong bang Don Vi Tinh
        /// </summary>
        /// <param name="dvt">object DonViTinh</param>
        public static void Update(DonViTinh dvt)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_UpdateDonViTinh", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@iDonViTinh_ID", SqlDbType.Int).Value = dvt.iDonViTinh_ID;
                sqlComm.Parameters.Add("@sTenDonViTinh", SqlDbType.NVarChar).Value = dvt.sTenDonViTinh;
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();               
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Insert mot ban ghi vao bang Don Vi Tinh
        /// </summary>
        /// <param name="dvt">object DonViTinh</param>
        public static void Insert(DonViTinh dvt)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_InsertDonViTinh", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;                
                sqlComm.Parameters.Add("@sTenDonViTinh", SqlDbType.NVarChar).Value = dvt.sTenDonViTinh;
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Delete mot ban ghi trong bang Don Vi Tinh
        /// </summary>
        /// <param name="id">iDonViTinh_ID</param>
        public static void Delete(int id)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_DeleteDonViTinh", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@iDonViTinh_ID", SqlDbType.Int).Value = id;
                sqlComm.ExecuteNonQuery();
                sqlComm.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Lấy các bản ghi của bảng Đơn Vị Tính theo chỉ số Trang
        /// </summary>
        /// <param name="pageIndex">PageIndex</param>
        /// <param name="pageSize">PageSize</param>
        /// <param name="totalRows">TotalRowsOfTable</param>
        /// <returns>DataTable</returns>
        public static DataTable SelectAllForPaging(int pageIndex, int pageSize,ref int totalRows)//out int totalRows)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                conn = ConnectDB.GetConnect();
                conn.Open();
                SqlCommand sqlComm = new SqlCommand("sp_SelectAllDonViTinhForPaging", conn);
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.Parameters.Add("@pageIndex", SqlDbType.Int).Value = pageIndex;
                sqlComm.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
                sqlComm.Parameters.Add("@totalRows", SqlDbType.Int).Value = 1;
                sqlComm.Parameters["@totalRows"].Direction = ParameterDirection.Output;
                SqlDataAdapter da = new SqlDataAdapter(sqlComm);
                DataTable dt = new DataTable();
                da.Fill(dt);
                totalRows = Convert.ToInt32(sqlComm.Parameters["@totalRows"].Value);
                da.Dispose();
                sqlComm.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

    }
}
